package cn.swenty.backstage.framework.service;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import cn.swenty.backstage.framework.common.util.StringUtil;
import cn.swenty.backstage.framework.database.plugin.PageView;

public abstract class JdbcService  {
	
	public abstract String getDriverClass();
	public abstract String getUrl();
	public abstract String getUserName();
	public abstract String getPassword();
	
	/**
	 * 创建一通新的数据库连接
	 * 
	 * @return
	 */
	public Connection newConnection() {
		Connection con = null;
		try {
			Class.forName(getDriverClass());
			if(StringUtil.isNotNull(getUserName())){
				con = DriverManager.getConnection(getUrl(),getUserName(),getPassword());
			}else{
				con = DriverManager.getConnection(getUrl());
			}
			
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return con;
	}
	
	public Map<String, Object> queryForMap(String sql, Object... objects) {
		List<Map<String, Object>> lists = queryForList(sql, objects);
		if (lists.size() > 1) {
			throw new RuntimeException("get result is not a single data");
		} else if (lists.size() == 1) {
			return lists.get(0);
		}
		return null;
	}

	public Object queryForObject(String sql, Object... objects) {
		List<Map<String, Object>> lists = queryForList(sql, objects);
		if (lists.size() == 0 ) {
			return null;
		}else if(lists.size() > 1){
			throw new RuntimeException("get result is not a single data");
		} else {
			Map<String, Object> map = lists.get(0);
			if (map.size() != 1) {
				throw new RuntimeException("get result is not a single data");
			} else {
				Iterator<Object> it = map.values().iterator();
				if (it.hasNext()) {
					return it.next();
				}
			}
		}
		return null;
	}

	public List<Map<String, Object>> queryForList(String sql) {

		List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();

		Connection connection = newConnection();

		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = connection.prepareStatement(sql);
			rs = ps.executeQuery();
			list = _recordMappingToMap(rs);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (rs != null)
					rs.close();
				if (ps != null)
					ps.close();
				if (connection != null)
					connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		return list;
	}

	public void execute(String sql, Object... objects) {
		Connection connection = newConnection();
		PreparedStatement ps = null;
		try {
			ps = connection.prepareStatement(sql);

			for (int i = 0; i < objects.length; i++) {
				ps.setObject(i + 1, objects[i]);
			}
			ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (ps != null)
					ps.close();
				if (connection != null)
					connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	/** impala 支持分页 */
	public PageView queryForPage(PageView pageView, String sql, Object... objects) {

		long count = 0;
		String countSql = " select count(1) c from (" + sql + ") v";
		List<Map<String, Object>> maps = queryForList(countSql, objects);
		if (maps != null && maps.size() > 0) {
			count = (long) maps.get(0).get("c");
		}
		pageView.setRowCount(count);
		String querySql = sql + " limit " + pageView.getPageSize() + " offset pageView.getFirstResult()";
		pageView.setRecords(queryForList(querySql, objects));
		return pageView;
	}

	public List<Map<String, Object>> queryForList(String sql, Object... objects) {

		List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();

		Connection connection = newConnection();

		PreparedStatement ps = null;
		ResultSet rs = null;

		try {
			ps = connection.prepareStatement(sql);

			for (int i = 0; i < objects.length; i++) {
				ps.setObject(i + 1, objects[i]);
			}
			rs = ps.executeQuery();
			list = _recordMappingToMap(rs);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (rs != null)
					rs.close();
				if (ps != null)
					ps.close();
				if (connection != null)
					connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		return list;

	}

	public void executeBatch(String sql, List<Object[]> list) {
		Connection connection = newConnection();
		PreparedStatement ps = null;
		try {
			connection.setAutoCommit(false);
			ps = connection.prepareStatement(sql);
			for (int i = 0; i < list.size(); i++) {
				Object[] os = list.get(i);
				for (int j = 0; j < os.length; j++) {
					ps.setObject(j + 1, os[j]);
				}
				ps.addBatch();
			}
			ps.executeBatch();
			connection.commit();
		} catch (SQLException e) {
			e.printStackTrace();
			try {
				connection.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
		} finally {
			try {
				if (ps != null)
					ps.close();
				if (connection != null)
					connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	
	/**
	 * 将ResultSet结果集中的记录映射到Map对象中.
	 * 
	 * @param fieldClassName
	 *            是JDBC API中的类型名称,
	 * @param fieldName
	 *            是字段名，
	 * @param rs
	 *            是一个ResultSet查询结果集,
	 * @param fieldValue
	 *            Map对象,用于存贮一条记录.
	 * @throws SQLException
	 */
	private List<Map<String, Object>> _recordMappingToMap(ResultSet rs) throws SQLException {
		List<Map<String, Object>> list = new ArrayList<>();
		ResultSetMetaData metaData = rs.getMetaData();
		while (rs.next()) {
			Map<String, Object> map = new HashMap<String, Object>() {
				/**
				* 
				*/
				private static final long serialVersionUID = 1L;

				@Override
				public Object put(String key, Object value) {
					return super.put(key.toLowerCase(), value);
				}

				@Override
				public Object get(Object key) {
					return super.get(key.toString().toLowerCase());
				}

			};
			for (int i = 1; i < metaData.getColumnCount() + 1; i++) {
				String fieldName = metaData.getColumnName(i);
				String fieldClassName = metaData.getColumnClassName(i);
				// String fieldClassName = "java.lang.String";
				
				// 优先规则：常用类型靠前
				Object s = null;
				try {
					if (fieldClassName.equals("java.lang.String")) {
						s = rs.getString(i);
					} else if (fieldClassName.equals("java.lang.Integer")) {
						s = rs.getInt(i);
					} else if (fieldClassName.equals("java.lang.Long")) {
						s = rs.getLong(i);
					} else if (fieldClassName.equals("java.lang.Boolean")) {
						s = rs.getBoolean(i);
					} else if (fieldClassName.equals("java.lang.Short")) {
						s = rs.getShort(i);
					} else if (fieldClassName.equals("java.lang.Float")) {
						s = rs.getFloat(i);
					} else if (fieldClassName.equals("java.lang.Double")) {
						s = rs.getDouble(i);
					} else if (fieldClassName.equals("java.sql.Timestamp")) {
						s = rs.getTimestamp(i);
					} else if (fieldClassName.equals("java.sql.Date") || fieldClassName.equals("java.util.Date")) {
						s = rs.getDate(i);
					} else if (fieldClassName.equals("java.sql.Time")) {
						s = rs.getTime(i);
					} else if (fieldClassName.equals("java.lang.Byte")) {
						s = rs.getByte(i);
					} else if (fieldClassName.equals("[B") || fieldClassName.equals("byte[]")) {
						// byte[]出现在SQL Server中
						s = rs.getBytes(i);
					} else if (fieldClassName.equals("java.math.BigDecimal")) {
						s = rs.getBigDecimal(i);
					} else if (fieldClassName.equals("java.lang.Object")
							|| fieldClassName.equals("oracle.sql.STRUCT")) {
						s = rs.getObject(i);
					} else if (fieldClassName.equals("java.sql.Array")
							|| fieldClassName.equals("oracle.sql.ARRAY")) {
						s = rs.getArray(i);
					} else if (fieldClassName.equals("java.sql.Clob")) {
						s = rs.getClob(i);
					} else if (fieldClassName.equals("java.sql.Blob")) {
						s = rs.getBlob(i);
					} else {// 对于其它任何未知类型的处理
						s = rs.getObject(i);
					}
					map.put(fieldName, s);
				} catch (Exception e) {
					e.fillInStackTrace();
					map.put(fieldName, null);
				}
			}
			list.add(map);

		}
		return list;

	}

}
